# -*- coding：utf-8 -*-

import os

import pandas as pd
import requests
import yaml
import datetime
from sqlalchemy import create_engine


class Mysql:
    def __init__(self):
        mysql_host = '10.111.96.203'
        mysql_user = 'root'
        mysql_password = 'MYsql123!'
        mysql_db = 'db_jh'
        mysql_charset = 'utf8mb4'
        port = 3306
        self.engine = create_engine(
            'mysql+pymysql://{}:{}@{}:{}/{}'.format(mysql_user, mysql_password, mysql_host, port, mysql_db))

    def to_sql(self, table_name, data):
        # 第一个参数是表名
        # 1.fail:如果表存在，啥也不做
        # 2.replace:如果表存在，删了表，再建立一个新表，把数据插入
        # 3.append:如果表存在，把数据插入，如果表不存在创建一个表！
        # index 是否储存index列
        data.to_sql(table_name, con=self.engine, if_exists='append', index=False)

    def get_sql(self, table_name):
        data = pd.read_sql(table_name, con=self.engine)
        return data


def get_excel(path):
    """
    excel文件读取(最新)
    :param path: 文件路径
    :return: excel文件数据
    """
    lists = os.listdir(path)
    lists.sort(key=lambda x: os.path.getatime((path + "\\" + x)))
    file_new = os.path.join(path, lists[-1])
    try:
        df_data = pd.read_excel(file_new)
        return df_data
    except Exception as e:
        print(e)


def read_data(yaml_name):
    """
    YAML文件读取
    :param yaml_name: 路径
    :return: yaml文件数据
    """
    try:
        with open(yaml_name, mode='r', encoding="utf8") as f:
            value = yaml.safe_load(f)
            return value
    except Exception as e:
        print(e)


def write_yaml(data, file_new):
    """
    YAML信息写入
    :param data: 数据
    :return:
    """
    if not os.path.exists(file_new):
        os.system(file_new)
    f = open(file_new, 'a', encoding='utf8')
    write_data = yaml.dump(data, allow_unicode=True, sort_keys=False)
    f.write(write_data)
    f.close()


def clear_yaml(filepath):
    """
    删除文件内容
    :param filepath: 路径
    """
    try:
        fo = open(filepath, 'w', encoding='utf8')
        fo.truncate()
    except Exception as e:
        print(e)


def data_processing(data):
    """
    筛选收费站字段
    :param data: 数据
    :return:
    """
    data_new = list()
    for i in range(len(data)):
        data_new.append(data[i]['收费站'])
    return data_new


def data_processing_m(data):
    """
    筛选收费站字段
    :param data: 数据
    :return:
    """
    data_new = list()
    for i in range(len(data)):
        data_new.append(data[i]['断面'])
    return data_new


def message(data_a, data_b, data_c, data_d, data_e, data_at, data_telephone, data_time, weather_day, weather_night,
            tempMax, tempMin):
    """
    钉钉消息推送
    :param data_a: 车道ETC成功率
    :param data_b: 车道入口车牌识别率
    :param data_c: 车道出口车牌识别率
    :param data_d: 门架ETC交易成功率
    :param data_e: 门架CPC交易成功率
    :param data_at: @负责人
    :param data_telephone: 负责人电话号码
    :param data_time: 时间
    :param weather_day: 白天天气
    :param weather_night: 夜晚天气
    :param tempMax: 最高温度
    :param tempMin: 最低温度
    :return: 消息文本
    """
    url = "https://oapi.dingtalk.com/robot/send?access_token=292beb298acd0e5310dbf8b835e8ad479298a980cea585072c678d446b443fa3"  # 金华机电群
    # url = "https://oapi.dingtalk.com/robot/send?access_token=4cf3a42bf47ecc4b9e166f2c6f4c9e899a7f1eaa8c5319db9260e5f8be103deb"  # 测试
    headers = {
        'Content-Type': 'application/json'
    }
    json = {
        "msgtype": "markdown",
        "markdown": {
            "title": "数据推送",
            "text": "![one](https://www.gaoliyun.com.cn/_/file/get_image?bucket=jdy-media&key=899d633c-d2a4-4cae-ad9c-56d309414e12&fileExt=.png&mode=lfit&height=1200&width=1200&deadline=1710989999)\n "
            # f"<font color=#006ad1>{data_time}&emsp;天气：{weather_day}&emsp;气温：{tempMin}~{tempMax}℃</font>"
                    f"{data_time}&emsp;金华：{weather_day}&emsp;气温：{tempMin}~{tempMax}℃"
                    "![two](https://www.gaoliyun.com.cn/_/file/get_image?bucket=jdy-media&key=fcf83cbf-c87e-4b6f-bc2f-7a150201d7c6&fileExt=.png&mode=lfit&height=1200&width=1200&deadline=1710989999)\n"
                    "<font color=#006ad1>**1. 车道ETC成功率小于99.5%：**</font> \n "
            # "**1. 车道ETC车道成功率小于99.5%：**\n"
                    f"{data_a}"
                    "\n\n<font color=#006ad1>**2. 车道入口车牌识别率小于99.5%:**</font>\n\n"
            # "\n**2. 车道入口车牌识别率小于99.5%:**\n"
                    f"{data_b}"
                    "\n\n<font color=#006ad1>**3. 车道出口车牌识别率小于99.5%:**</font>\n\n"
            # "\n**3. 车道出口车牌识别率小于99.5%:**\n"
                    f"{data_c}"
                    "\n\n<font color=#006ad1>**4. 门架ETC交易成功率小于99.5%:**</font>\n\n"
            # "\n**4. 门架ETC交易成功率小于99.7%：**\n"
                    f"{data_d}"
                    "\n\n<font color=#006ad1>**5. 门架CPC交易成功率小于99.5%:**</font>\n\n"
            # "\n**5. 门架CPC交易成功率小于99.5%：**\n"
                    f"{data_e}\n\n"
                    "![three](https://www.gaoliyun.com.cn/_/file/get_image?bucket=jdy-media&key=b5b63c73-9913-4685-9f40-c8eb2cc26896&fileExt=.png&mode=lfit&height=1200&width=1200&deadline=1710989999)\n"
                    f"{data_at}"
                    "\n\n“<font color=#F5222F>**+n**</font>”表示本周低于指标的累计次数"
                    "\n\n【[查看详情](https://www.gaoliyun.com.cn/app/623032491fc1e50007e87ee3/entry/65dc26ec0327dc70f7354757)】"
                    "浙高运数字办提供支持"

        },
        "at": {
            'atMobiles': data_telephone,
            "isAtAll": False,
        }

    }
    return requests.post(url=url, headers=headers, json=json)


def screen_data(de, item, number):
    """
    数据筛选
    :param de: execl数据
    :param item: 指标
    :param number: 范围
    :return: 未达标数据
    """
    some = de[de[item] < number]
    if not some.empty:
        Mysql().to_sql('lane', some)
        try:
            some = some[["收费站", item]]
            some = some.sort_values(by=item, ascending=False)
            some_data = some.to_dict(orient="records")
            return some_data
        except Exception as e:
            print(e)
    else:
        return []


def gantry_screen(de, item, number):
    """
    门架数据筛选
    :param de: execl数据
    :param item: 名称
    :param number:指标
    :return: 未达标数据
    """
    s = de[de[item] < number]
    if not s.empty:
        try:
            Mysql().to_sql('gantry', s)
            s = s[["收费站", "断面", item]]
            s = s.sort_values(by=item, ascending=False)
            some_data = s.to_dict(orient="records")
            return some_data
        except Exception as e:
            print(e)
    else:
        return []


def weather(location):
    """
    天气提取
    :return: 当日天气数据
    """
    url = "https://devapi.qweather.com/v7/weather/3d?"
    params = {
        'key': 'f0d50344084148219207996b9fb59225',
        'location': location
    }
    r = requests.get(url=url, params=params)
    return r.json()


def handle(data):
    """
    信息排版
    :param data: 信息
    :return: 文本格式信息
    """
    text = str()
    if len(data) != 0:
        for i in range(len(data)):
            if 'ETC车道成功率' in data[i].keys():
                text += "- 收费站：{a}&nbsp;-&nbsp;{b}%<font color=#F5222F>&nbsp;&nbsp;+{c}</font>\n".format(
                    a=data[i]['收费站'],
                    b=f"{(data[i]['ETC车道成功率'] * 100):.2f}",
                    c=data[i]['累计'])
            elif '入口车牌识别率' in data[i].keys():
                text += "- 收费站：{a}&nbsp;-&nbsp;{b}%<font color=#F5222F>&nbsp;&nbsp;+{c}</font>\n".format(
                    a=data[i]['收费站'],
                    b=f"{(data[i]['入口车牌识别率'] * 100):.2f}",
                    c=data[i]['累计'])
            elif '出口车牌识别率' in data[i].keys():
                text += "- 收费站：{a}&nbsp;-&nbsp;{b}%<font color=#F5222F>&nbsp;&nbsp;+{c}</font>\n".format(
                    a=data[i]['收费站'],
                    b=f"{(data[i]['出口车牌识别率'] * 100):.2f}",
                    c=data[i]['累计'])
        return text
    else:
        return "- 无\n\n"


def handle_m(data):
    """
    信息排版
    :param data: 信息
    :return: 文本格式信息
    """
    text = str()
    if len(data) != 0:
        for i in range(len(data)):
            if 'ETC交易成功率' in data[i].keys():
                text += "- {a}&nbsp;-&nbsp;{b}&nbsp;-&nbsp;{c}%<font color=#F5222F>&nbsp;&nbsp;+{d}</font>\n".format(
                    a=data[i]['收费站'],
                    b=data[i]['断面'],
                    c=f"{(data[i]['ETC交易成功率'] * 100):.2f}",
                    d=data[i]['累计'])
            elif 'CPC交易成功率' in data[i].keys():
                text += "- {a}&nbsp;-&nbsp;{b}&nbsp;-&nbsp;{c}%<font color=#F5222F>&nbsp;&nbsp;+{d}</font>\n".format(
                    a=data[i]['收费站'],
                    b=data[i]['断面'],
                    c=f"{(data[i]['CPC交易成功率'] * 100):.2f}",
                    d=data[i]['累计'])
        return text
    else:
        return "- 无\n\n"


def time():
    """
    获取时间
    :return: 时间
    """
    now_time = datetime.datetime.now().date()
    weekday = datetime.datetime.today().weekday()
    week_list = ["星期一", "星期二", "星期三", "星期四", "星期五", "星期六", "星期日"]
    now_week = week_list[weekday]
    if now_week == '星期日':
        clear_yaml('store_etc.yaml')
        clear_yaml('store_standard.yaml')
        clear_yaml('store_out.yaml')
        clear_yaml('gantry_etc_standard.yaml')
        clear_yaml('gantry_cpc_standard.yaml')
    return str(now_time) + "  " + str(now_week)


name = list()
telephone = list()
today = datetime.date.today()
yesterday = today - datetime.timedelta(days=1)
# --------------------车道监测指标-------------------------
df = get_excel("E:/芸数据")
df.set_axis(df.iloc[1], axis=1, inplace=True)
df.insert(0, 'time', yesterday)
df.drop(df[df["管理处"] != "金华管理中心"].index, inplace=True)
# 筛选车道ETC成功率小于0.995的数据
some1 = screen_data(df, "ETC车道成功率", 0.995)
if some1:
    write_yaml(data_processing(some1), './store_etc.yaml')
    store_etc = read_data('store_etc.yaml')
    for i in range(len(some1)):
        a = 0
        for j in store_etc:
            if some1[i]['收费站'] == j:
                a += 1
        some1[i]['累计'] = a
# 筛选车道入口车牌识别率小于0.995的数据
some2 = screen_data(df, "入口车牌识别率", 0.995)
if some2:
    write_yaml(data_processing(some2), './store_standard.yaml')
    store_standard = read_data('store_standard.yaml')
    for i in range(len(some2)):
        b = 0
        for j in store_standard:
            if some2[i]['收费站'] == j:
                b += 1
        some2[i]['累计'] = b
# 筛选车道出口车牌识别率小于0.995的数据
some3 = screen_data(df, "出口车牌识别率", 0.995)
if some3:
    write_yaml(data_processing(some3), './store_out.yaml')
    store_out = read_data('store_out.yaml')
    for i in range(len(some3)):
        c = 0
        for j in store_out:
            if some3[i]['收费站'] == j:
                c += 1
        some3[i]['累计'] = c
# --------------------ETC门架监测指标------------------------
df1 = get_excel("E:\芸数据（门架）")
df1.set_axis(df1.iloc[1], axis=1, inplace=True)
df1.insert(0, 'time', yesterday)
df1.drop(df1[df1["管理处"] != "金华管理中心"].index, inplace=True)
# 筛选门架ETC交易成功率小于0.997的数据
some4 = gantry_screen(df1, "ETC交易成功率", 0.995)
if some4:
    write_yaml(data_processing_m(some4), './gantry_etc_standard.yaml')
    gantry_etc_standard = read_data('gantry_etc_standard.yaml')
    for i in range(len(some4)):
        d = 0
        for j in gantry_etc_standard:
            if some4[i]['断面'] == j:
                d += 1
        some4[i]['累计'] = d
# 筛选门架cpc交易成功率小于0.995的数据
some5 = gantry_screen(df1, "CPC交易成功率", 0.995)
if some5:
    write_yaml(data_processing_m(some5), './gantry_cpc_standard.yaml')
    gantry_cpc_standard = read_data('gantry_cpc_standard.yaml')
    for i in range(len(some5)):
        e = 0
        for j in gantry_cpc_standard:
            if some5[i]['断面'] == j:
                e += 1
        some5[i]['累计'] = e
# --------------------负责人关联-------------------------
name = list()
telephone = list()
address_book = read_data("./data.yaml")
data_all = some1 + some2 + some3 + some4 + some5
for i in range(len(data_all)):
    name.append(data_all[i]["收费站"])
name = list(set(name))
for i in name:
    for j in address_book["address_book"]:
        if i == j:
            telephone.append(address_book["address_book"][j])
# --------------------消息处理发送-------------------------
at = str()
telephone = list(set(telephone))
telephone.append(13857927334)
print(telephone)
telephone = list(map(str, telephone))
if len(telephone) > 0:
    for i in telephone:
        at += f"@{i}"
text_etc = handle(some1)
text_in = handle(some2)
text_out = handle(some3)
text_gantry_etc = handle_m(some4)
text_gantry_cpc = handle_m(some5)
text_time = time()
text_weather_day = weather(101210901)['daily'][0]['textDay']
text_weather_night = weather(101210901)['daily'][0]['textNight']
text_temperature_tempMax = weather(101210901)['daily'][0]['tempMax']
text_temperature_tempMin = weather(101210901)['daily'][0]['tempMin']
res = message(text_etc, text_in, text_out, text_gantry_etc, text_gantry_cpc, at, telephone, text_time, text_weather_day,
              text_weather_night, text_temperature_tempMax, text_temperature_tempMin)
print(res.json())
